package br.com.gestantes.controle;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import br.com.gestantes.modelo.Estoque;
import br.com.gestantes.modelo.serraria.PedidoSerraria;

/**
 * Handles requests for the application home page.
 */
@Controller
public class HomeController {
	
	private static final Logger logger = LoggerFactory.getLogger(HomeController.class);
	
	/**
	 * Simply selects the home view to render by returning its name.
	 */
	@RequestMapping(value = "/", method = RequestMethod.GET)
	public String home(Locale locale, Model model) {
		
		logger.info("Welcome home! "+ locale.toString());
		
		//TODO Passar para o banco de dados
		Estoque estoque = new Estoque();
		
		Date date = new Date();
		
	    SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy, HH:mm:ss");
		
		String formattedDate = dateFormat.format(date);
		
		model.addAttribute("serverTime", formattedDate );
		model.addAttribute("pedidos", listaPedidosSerrariaNovos() );
		
		return "home";
	}
	
	private List<PedidoSerraria> listaPedidos(String status){
		
		List<PedidoSerraria> pedidos = new ArrayList<PedidoSerraria>();
		
		Connection con = null;
        Statement st = null;
        ResultSet rs = null;
    
        String driver = "org.postgresql.Driver";
        String url = "jdbc:postgresql://localhost:5432/gestantes";
        String user = "root";
        String password = "123";

        try {
            Class.forName(driver).newInstance();
            con = DriverManager.getConnection(url, user, password);
            st = con.createStatement();
            rs = st.executeQuery("SELECT ped_serr_sq_pedido_serraria, ped_serr_sq_usuario, ped_serr_dt_data, ped_serr_sq_item, ped_serr_int_qtd, ped_serr_enum_status, b.usua_nm_nome, b.usua_sq_usuario FROM PEDIDOS_SERRARIA a, usuario b WHERE ped_serr_sq_usuario=usua_sq_usuario AND ped_serr_enum_status = '"+status+"'");

            while(rs.next()) {
            	
            	PedidoSerraria pedido = new PedidoSerraria();
            	
            	pedido.setNumeroPedido(rs.getLong("ped_serr_sq_pedido_serraria"));
            	pedido.setIdUsuario(rs.getInt("ped_serr_sq_usuario"));
            	pedido.setNomeSolicitante(rs.getString("usua_nm_nome"));
            	pedido.setData(rs.getTimestamp("ped_serr_dt_data"));
            	
            	pedido.setIdItem(rs.getInt("ped_serr_sq_item"));
            	pedido.setNomeItem(Estoque.produtos.get(pedido.getIdItem()).getNome());
            	
            	pedido.setQuantidade(rs.getInt("ped_serr_int_qtd"));
            	pedido.setStatus(rs.getString("ped_serr_enum_status"));
            	            	
            	pedidos.add(pedido);
            }

        } catch (SQLException ex) {
            
            ex.printStackTrace();

        } catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
               ex.printStackTrace();
            }
        }
		
        return pedidos;
	}
	
	private List<PedidoSerraria> listaPedidosSerrariaNovos(){
		
		return listaPedidos("Novo");
	}
		
}
